﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

namespace InhollandAir.App_Code
{
    public class User
    {
        private int mId;
        private string mFirstname;
        private string mLastname;
        private string mUsername;
        private string mEmail;
        private string mPassword;
        private string mAddress;
        private string mZipcode;
        private string mCity;
        private string mCountry;
        private string mPhonenr;
        private string mBirthdate;
        private int mAirmiles;
        private Dictionary<int, Flight> mBookedFlights; //reservation id with flight object

        public User()
        {
            //make empty user;
            mBookedFlights = new Dictionary<int,Flight>();
        }

        public User(string firstname, string lastname, string username, string email, string password, string address, string zipcode, string city, string country, string phonenr, string birthdate)
        {
            mFirstname = firstname;
            mLastname = lastname;
            mUsername = username;
            mEmail = email;
            mPassword = password;
            mAddress = address;
            mZipcode = zipcode;
            mCity = city;
            mCountry = country;
            mPhonenr = phonenr;
            mBirthdate = birthdate;
            //mAirmiles = airmiles;
            mBookedFlights = new Dictionary<int, Flight>();
        }

        public void Save()
        {

            if (id == 0)
            {
                try
                {
                    SaveNewAccount();
                    Logging.WriteActivity("Created new account and saved it in db, accountID: " + mId);
                }
                catch (SqlException sqlEx)
                {
                    Logging.WriteError(sqlEx.Message + ". Username: " + this.username);
                    throw new Exception("Database unavailable, please try again later.");
                }
                catch (Exception ex)
                {
                    Logging.WriteError(ex.Message + ". Username: " + this.username);
                    throw new Exception("Failed to create account, please contact the admin");
                }
            }
            else
            {
                try
                {
                    UpdateAccount();
                    Logging.WriteActivity("Updated account and saved it in db, accountID: " + mId);
                }
                catch (SqlException sqlEx)
                {                    
                    Logging.WriteError(sqlEx.Message + ". UserID: " + this.mId);
                    throw new Exception("Database unavailable, please try again later.");
                }
                catch (Exception ex)
                {
                    Logging.WriteError(ex.Message + ". UserID: " + this.mId);
                    throw new Exception("Failed to create account, please contact the admin");
                }
            }

        }
        private void SaveNewAccount()
        {
            var conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ReadUser"].ToString());

            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                const string sqlText = "INSERT INTO account (firstname, lastname, username, email, password, address, zipcode, city, country, phonenr, birthdate, airmiles) " +
                                  "VALUES(@firstname, @lastname, @username, @email, @password, @address, @zipcode, @city, @country, @phonenr, CONVERT(char(10), @birthdate,126), @airmiles)";
                SqlCommand cmd = new SqlCommand(sqlText, conn);
                //string password = new Tools().EncodePassword(mPassword);
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@firstname", mFirstname);
                cmd.Parameters.AddWithValue("@lastname", mLastname);
                cmd.Parameters.AddWithValue("@username", mUsername);
                cmd.Parameters.AddWithValue("@email", mEmail);
                cmd.Parameters.AddWithValue("@password", mPassword);
                cmd.Parameters.AddWithValue("@address", mAddress);
                cmd.Parameters.AddWithValue("@zipcode", mZipcode);
                cmd.Parameters.AddWithValue("@city", mCity);
                cmd.Parameters.AddWithValue("@country", mCountry);
                cmd.Parameters.AddWithValue("@phonenr", mPhonenr);
                cmd.Parameters.AddWithValue("@birthdate", mBirthdate);
                cmd.Parameters.AddWithValue("@airmiles", 1500);

                cmd.ExecuteNonQuery();

                //Get new ID from user
                const string sqlTextGetID = "SELECT accountID FROM account WHERE username = @username";
                cmd = new SqlCommand(sqlTextGetID, conn);

                cmd.Parameters.AddWithValue("@username", mUsername);

                SqlDataReader myReader = cmd.ExecuteReader();

                if (myReader.Read())
                {
                    this.mId = (int)myReader["accountID"];
                }
                              
            }
            catch (SqlException sqlEx)
            {
                throw sqlEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }  
            finally
            {
                conn.Close();
            }
        }
        private void UpdateAccount()
        {
            var conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ReadUser"].ToString());

            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                const string sqlText = "UPDATE account SET firstname=@firstname, lastname=@lastname, username=@username, email=@email, address=@address, zipcode=@zipcode,"
                + "city=@city, country=@country, phonenr=@phonenr, birthdate=@birthdate, airmiles=@airmiles WHERE accountID = @accountID";
                SqlCommand cmd = new SqlCommand(sqlText, conn);

                cmd.Prepare();

                cmd.Parameters.AddWithValue("@accountID", mId);
                cmd.Parameters.AddWithValue("@firstname", mFirstname);
                cmd.Parameters.AddWithValue("@lastname", mLastname);
                cmd.Parameters.AddWithValue("@username", mUsername);
                cmd.Parameters.AddWithValue("@email", mEmail);
                cmd.Parameters.AddWithValue("@address", mAddress);
                cmd.Parameters.AddWithValue("@zipcode", mZipcode);
                cmd.Parameters.AddWithValue("@city", mCity);
                cmd.Parameters.AddWithValue("@country", mCountry);
                cmd.Parameters.AddWithValue("@phonenr", mPhonenr);
                cmd.Parameters.AddWithValue("@birthdate", mBirthdate);
                cmd.Parameters.AddWithValue("@airmiles", mAirmiles);

                cmd.ExecuteNonQuery();
                Logging.WriteActivity("Updated account and saved it in db, accountID: " + id);

            }
            catch (SqlException sqlEx)
            {
                throw sqlEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void InsertSalt(int userId, string salt)
        {
            var conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SaltTable"].ToString());

            if (conn.State == ConnectionState.Closed) conn.Open();

            try
            {
                const string sqlText = "INSERT INTO salt(FKaccountId, salt) " +
                                  "VALUES(@userId, @salt)";
                SqlCommand cmd = new SqlCommand(sqlText, conn);
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@userId", userId);
                cmd.Parameters.AddWithValue("@salt", salt);

                cmd.ExecuteNonQuery();
                Logging.WriteActivity("Inserted salt, accountID: " + userId);
            }
            catch (SqlException sqlEx)
            {
                //log exception
                Logging.WriteError(sqlEx.Message + ". UserID: " + userId);
                //throw new exception to user
                throw new Exception("Database unavailable, please try again later.");
            }
            catch (Exception ex)
            {
                //log exception
                Logging.WriteError(ex.Message + ". UserID: " + userId);
                //throw new exception to user
                throw new Exception("Failed to create account, please contact the admin");
            }
            finally
            {
                conn.Close();
            }

        }

        public bool Login(string username, string password)
        {
            //get ID
            //check username in db
            var connUser = new SqlConnection(WebConfigurationManager.ConnectionStrings["ReadUser"].ToString());

            try
            {
                if (connUser.State == ConnectionState.Closed) connUser.Open();

                string sqlText = "SELECT accountID FROM account WHERE username = @username";
                SqlCommand cmd = new SqlCommand(sqlText, connUser);
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@username", username);

                SqlDataReader myReader = cmd.ExecuteReader();

                if (myReader.Read())
                {
                    this.mId = (int)myReader["accountID"];
                }
                else
                {
                    throw new Exception("Username not found in db.");
                }
            }
            catch (SqlException sqlEx)
            {
                //log exception
                Logging.WriteError(sqlEx.Message + ". Username: " + username);
                //throw new exception to user
                throw new Exception("Database unavailable, please try again later.");
            }
            catch (Exception ex)
            {
                //log exception
                Logging.WriteError(ex.Message + ". Username: " + username);
                //throw new exception to user
                throw new Exception("Invalid username or password.");
            }
            finally
            {
                connUser.Close();
            }

            //get salt
            string salt = "";
            var connSalt = new SqlConnection(WebConfigurationManager.ConnectionStrings["SaltTable"].ToString());

            if (connSalt.State == ConnectionState.Closed) connSalt.Open();

            try
            {
                string sqlText = "SELECT salt FROM salt WHERE FKaccountID = @ID";
                SqlCommand cmd = new SqlCommand(sqlText, connSalt);
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@ID", mId);
                SqlDataReader myReader = cmd.ExecuteReader();

                if (myReader.Read())
                {
                    salt = myReader["salt"].ToString();
                }
                else
                {
                    throw new Exception("Salt not found in db.");
                }
            }
            catch (SqlException sqlEx)
            {
                //log exception
                Logging.WriteError(sqlEx.Message + ". UserID: " + mId);
                //throw new exception to user
                throw new Exception("Database unavailable, please try again later.");
            }
            catch (Exception ex)
            {
                //log exception
                Logging.WriteError(ex.Message + ". UserID: " + mId);
                //throw new exception to user
                throw new Exception("Please reset your password.");
            }
            finally
            {
                connSalt.Close();
            }

            //encode password
            password = Tools.EncodePassword(password, salt);

            try
            {
                //check username and password in db
                if (connUser.State == ConnectionState.Closed) connUser.Open();

                string sqlText = "SELECT * FROM account WHERE username = @username AND password = @password";

                SqlCommand cmd = new SqlCommand(sqlText, connUser);

                cmd.Prepare();

                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@password", password);

                SqlDataReader myReader = cmd.ExecuteReader();

                if (myReader.Read())
                {
                    this.mId = (int)myReader["accountID"];
                    this.mFirstname = myReader["firstname"].ToString();
                    this.mLastname = myReader["lastname"].ToString();
                    this.mUsername = myReader["username"].ToString();
                    this.mEmail = myReader["email"].ToString();
                    this.mAddress = myReader["address"].ToString();
                    this.mZipcode = myReader["zipcode"].ToString();
                    this.mCity = myReader["city"].ToString();
                    this.mCountry = myReader["country"].ToString();
                    this.mPhonenr = myReader["phonenr"].ToString();
                    this.mBirthdate = myReader["birthdate"].ToString();
                    this.mAirmiles = (int)myReader["airmiles"];
                }
                else
                {
                    throw new Exception("Wrong password!");
                }
            }
            catch (SqlException sqlEx)
            {
                //log exception
                Logging.WriteError(sqlEx.Message + ". UserID: " + mId);
                //throw new exception to user
                throw new Exception("Database unavailable, please try again later.");
            }
            catch (Exception ex)
            {
                //log exception
                Logging.WriteError(ex.Message + ". UserID: " + mId);
                //throw new exception to user
                throw new Exception("Invalid username or password.");
            }
            finally
            {
                connUser.Close();
            }
            return true;
        }
        public bool Load(string sessionId, Dictionary<string, User> activeUsersCache)
        {
            //to load user that is logged in use: user.Load(Session.SessionID, (Dictionary<string, InhollandAir.App_Code.User>)Application["ActiveUsers"]);

            User user = new User();
            if (activeUsersCache.ContainsKey(sessionId))
            {
                user = activeUsersCache[sessionId];
                this.mId = user.id;
                this.mFirstname = user.firstname;
                this.mLastname = user.lastname;
                this.username = user.username;
                this.mEmail = user.email;
                //this.mPassword = user.password;
                this.mAddress = user.address;
                this.mZipcode = user.zipcode;
                this.mCity = user.city;
                this.mCountry = user.country;
                this.mPhonenr = user.phonenr;
                this.mBirthdate = user.birthdate;
                this.mAirmiles = user.airmiles;
            }
            else
            {
                return false;
            }
            return true;
        }
        public void LoadBookedFlightsFromDb()
        {
            //get flight ids
            var connUser = new SqlConnection(WebConfigurationManager.ConnectionStrings["FlightReservation"].ToString());

            if (connUser.State == ConnectionState.Closed) connUser.Open();

            try
            {
                string sqlText = "SELECT * FROM reservation INNER JOIN flight ON reservation.flightID=flight.flightID " + 
                    "WHERE accountID = @accountId Order By flight.departureDate DESC";
                SqlCommand cmd = new SqlCommand(sqlText, connUser);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@accountId", mId);
                SqlDataReader myReader = cmd.ExecuteReader();                
                while (myReader.Read())
                {
                    Flight flight = new Flight();
                    int reservationId = (int)myReader["reservationID"];
                    int flightId = (int)myReader["flightID"];
                    flight.LoadFlightFromDb(flightId);
                    this.mBookedFlights.Add(reservationId, flight);
                }

            }
            catch (SqlException sqlEx)
            {
                Logging.WriteError(sqlEx.Message + ". UserID: " + this.mId);
                throw new Exception("Database unavailable, please try again later.");
            }
            catch (Exception ex)
            {
                Logging.WriteError(ex.Message + ". UserID: " + this.mId);
                throw new Exception("Failed to load booked flights, please contact the admin.");
            }
            finally
            {
                connUser.Close();
            }   
        }
        public void RemoveReservation(int reservationId)
        {
            //remove reservation
            var connUser = new SqlConnection(WebConfigurationManager.ConnectionStrings["FlightReservation"].ToString());

            if (connUser.State == ConnectionState.Closed) connUser.Open();

            try
            {
                //load flight with FlightID from the reservation
                Flight flight = new Flight(reservationId);
                //delete reservation
                string sqlText = "DELETE FROM reservation WHERE reservationID = @reservationId AND accountID = @accountId; ";               
                SqlCommand cmd = new SqlCommand(sqlText, connUser);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@reservationId", reservationId);
                cmd.Parameters.AddWithValue("@accountId", mId);
                int rowsAffected = cmd.ExecuteNonQuery();
                if (rowsAffected < 1)
                {
                    Exception ex = new Exception("No rows affected");
                    throw ex;
                }
                else
                {                    
                    //give user his airmiles back
                    mAirmiles += int.Parse(flight.price);
                    Save();
                }
            }
            catch (Exception ex)
            {
                //log exception?
                throw ex;
            }
            finally
            {
                connUser.Close();
            }   
        }

        #region Properties

        public int id
        {
            get { return mId; }
            set { mId = value; }
        }

        public string firstname
        {
            get { return mFirstname; }
            set { mFirstname = value; }
        }
        public string lastname
        {
            get { return mLastname; }
            set { mLastname = value; }
        }
        public string username
        {
            get { return mUsername; }
            set { mUsername = value; }
        }
        public string email
        {
            get { return mEmail; }
            set { mEmail = value; }
        }
        public void password(string password)
        {
            mPassword = password;
        }
        public string address
        {
            get { return mAddress; }
            set { mAddress = value; }
        }
        public string zipcode
        {
            get { return mZipcode; }
            set { mZipcode = value; }
        }
        public string city
        {
            get { return mCity; }
            set { mCity = value; }
        }
        public string country
        {
            get { return mCountry; }
            set { mCountry = value; }
        }
        public string phonenr
        {
            get { return mPhonenr; }
            set { mPhonenr = value; }
        }
        public string birthdate
        {
            get { return mBirthdate; }
            set { mBirthdate = value; }
        }
        public int airmiles
        {
            get { return mAirmiles; }
            set { mAirmiles = value; }
        }
        public Dictionary<int,Flight> bookedFlights
        {
            get { return mBookedFlights; }
            set { mBookedFlights = value; }
        }
        #endregion
    }
}